package oracle

import (
	"bytes"
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"os"
	"reflect"
	"sync"
	"time"

	"github.com/sijms/go-ora/v2/network"

	"github.com/pkg/errors"

	ora "github.com/sijms/go-ora/v2"
	"testkjcld.bhecard.com/gitlab/kit/esim/log"
)

type OraDB struct {
	*sql.DB
	dsn    string
	name   string
	schema string
	logger log.Logger
	cache  sync.Map
}

func NewOraDB(logger log.Logger, cfg *DbConfig) *OraDB {
	var err error
	var DB *sql.DB

	DB, err = sql.Open("oracle", cfg.Dsn)
	if err != nil {
		logger.Panicf("[db] %s open error : %s", cfg.Db, err.Error())
		return nil
	}
	err = DB.Ping()
	if err != nil {
		logger.Panicf("[db] %s sql.DB ping error : %s", cfg.Db, err.Error())
		return nil
	}
	DB.SetMaxOpenConns(cfg.MaxOpen)
	DB.SetMaxIdleConns(cfg.MaxIdle)
	DB.SetConnMaxLifetime(time.Duration(cfg.MaxLifetime) * time.Minute)

	return &OraDB{
		DB:     DB,
		dsn:    cfg.Dsn,
		name:   cfg.Db,
		schema: cfg.Schema,
		logger: logger,
	}
}

func setOraEnv() {
	_ = os.Setenv("NLS_LANG", "AMERICAN_AMERICA.AL32UTF8")
	_ = os.Setenv("NLS_TIMESTAMP_FORMAT", "YYYY-MM-DD HH24:MI:SS.FF6")
	_ = os.Setenv("NLS_TIMESTAMP_FORMAT", "YYYY-MM-DD HH24:MI:SS.FF6")
}

func (oraDB *OraDB) OraConnect(ctx context.Context) (*ora.Connection, error) {
	conn, _ := ora.NewConnection(oraDB.dsn)
	err := conn.OpenWithContext(ctx)
	if err != nil {
		oraDB.logger.Errorc(ctx, "Connect to oracle [%s] fail[%s]", oraDB.dsn, err)
		return nil, err
	}
	return conn, nil
}

type TableInfo struct {
	TableName    string
	ColNum       int
	QueryColumns []string      `json:"query_columns"` // 远端列名称
	Columns      []string      `json:"columns"`       // 列名称
	ColumnTypes  []string      `json:"column_types"`  // 列的数据类型
	PlaceHolders []string      `json:"place_holders"` // 列的数据类型
	Data         []interface{} `json:"data"`          // 字段Value
	ZeroFlag     []bool        `json:"zero"`          // 字段是否为空
	Scope        []string      `json:"scope"`         // 字段使用范围
}

type TableName interface {
	TableName() string
}

type contextTxKeyStruct struct{}

var contextTxKey = contextTxKeyStruct{}

func (oraDB *OraDB) BeginTx(ctx context.Context) (context.Context, *sql.Tx, error) {
	var err error
	tx, err := oraDB.DB.BeginTx(ctx, nil)
	if err != nil {
		return ctx, tx, err
	}
	return context.WithValue(ctx, contextTxKey, tx), tx, nil
}

func (oraDB *OraDB) GetCtxTx(ctx context.Context) *sql.Tx {
	if tx, ok := ctx.Value(contextTxKey).(*sql.Tx); ok {
		return tx
	}
	return nil
}

// 获取链接对应schema名称
func (oraDB *OraDB) Schema() string {
	return oraDB.schema
}

func (oraDB *OraDB) BulkCreateEntity(ctx context.Context, conn *ora.Connection, entityList []interface{}) (int64, error) {
	var tableName string
	var execSql string
	var err error
	rowNum := len(entityList)
	if rowNum == 0 {
		return 0, nil
	}

	entity := entityList[0]

	if strum, ok := entity.(TableName); ok {
		tableName = strum.TableName()
	} else {
		oraDB.logger.Errorc(ctx, "传入结构体非法:不能获取TableName")
		return 0, errors.Errorf("传入结构体非法;")
	}

	if cacheSql, ok := oraDB.cache.Load(oraDB.name + tableName); ok {
		// 缓存SQL
		execSql = cacheSql.(string)
	} else {
		// 非缓存
		var tableColumns bytes.Buffer
		var placeHolder bytes.Buffer
		var tableInfo *TableInfo

		tableInfo, err = oraDB.ProcessEntity2Table(entity)
		if err != nil {
			oraDB.logger.Errorc(ctx, "解析结构体指针失败[%s]", err)
			return 0, err
		}
		// 组装create语句
		for i := 0; i < tableInfo.ColNum; i++ {
			tableColumns.WriteString(tableInfo.Columns[i] + ",")
			if tableInfo.ZeroFlag[i] && tableInfo.ColumnTypes[i] == NullTimeStampName {
				placeHolder.WriteString("SYSTIMESTAMP,")
			} else {
				placeHolder.WriteString(tableInfo.PlaceHolders[i] + ",")
			}
		}
		tableColumns.Truncate(tableColumns.Len() - 1)
		placeHolder.Truncate(placeHolder.Len() - 1)

		execSql = fmt.Sprintf("insert into %s (%s) values (%s)", tableInfo.TableName, tableColumns.String(), placeHolder.String())
		// 缓存SQl
		oraDB.cache.Store(oraDB.name+tableInfo.TableName, execSql)
	}

	// 行数据转换
	var colNum int
	var args [][]driver.Value
	for idx, entity := range entityList {
		rowValue, err := oraDB.ProcessEntity2Args(entity)
		if err != nil {
			oraDB.logger.Errorc(ctx, "解析结构体值失败[%s]", err)
			return 0, err
		}
		// 首次初始化
		if idx == 0 {
			colNum = len(rowValue) // 列数目
			args = make([][]driver.Value, colNum)
			for j := 0; j < colNum; j++ {
				args[j] = make([]driver.Value, rowNum)
			}
		}
		// args赋值
		for k := 0; k < colNum; k++ {
			args[k][idx] = rowValue[k]
		}
	}
	oraDB.logger.Debugc(ctx, "执行插入SQL[%s][%+v]", execSql, args)

	// 没有事物
	result, err := conn.BulkInsert(execSql, rowNum, args...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

func (oraDB *OraDB) CreateEntity(ctx context.Context, entity interface{}) (int64, error) {
	var tableName string
	var args []interface{}
	var execSql string
	var err error

	if stru, ok := entity.(TableName); ok {
		tableName = stru.TableName()
	} else {
		oraDB.logger.Errorc(ctx, "传入结构体非法:不能获取TableName")
		return 0, errors.Errorf("传入结构体非法")
	}

	if cacheSql, ok := oraDB.cache.Load(oraDB.name + tableName); ok {
		// 缓存SQL
		execSql = cacheSql.(string)
		args, err = oraDB.ProcessEntity2Args(entity)
		if err != nil {
			oraDB.logger.Errorc(ctx, "解析结构体值失败[%s]", err)
			return 0, err
		}
	} else {
		// 非缓存
		var tableColumns bytes.Buffer
		var placeHolder bytes.Buffer
		var tableInfo *TableInfo

		tableInfo, err = oraDB.ProcessEntity2Table(entity)
		if err != nil {
			oraDB.logger.Errorc(ctx, "解析结构体指针失败[%s]", err)
			return 0, err
		}
		// 组装create语句
		for i := 0; i < tableInfo.ColNum; i++ {
			tableColumns.WriteString(tableInfo.Columns[i] + ",")
			if tableInfo.ZeroFlag[i] && tableInfo.ColumnTypes[i] == NullTimeStampName {
				placeHolder.WriteString("SYSTIMESTAMP,")
			} else {
				placeHolder.WriteString(tableInfo.PlaceHolders[i] + ",")
				args = append(args, tableInfo.Data[i])
			}
		}
		tableColumns.Truncate(tableColumns.Len() - 1)
		placeHolder.Truncate(placeHolder.Len() - 1)

		execSql = fmt.Sprintf("insert into %s (%s) values (%s)", tableInfo.TableName, tableColumns.String(), placeHolder.String())
		// 缓存SQl
		oraDB.cache.Store(oraDB.name+tableInfo.TableName, execSql)
	}

	oraDB.logger.Debugc(ctx, "执行插入SQL[%s][%+v]", execSql, args)

	if tx := oraDB.GetCtxTx(ctx); tx != nil {
		result, err := tx.Exec(execSql, args...)
		if err != nil {
			return 0, err
		}
		return result.RowsAffected()
	}
	// 没有事物
	result, err := oraDB.DB.Exec(execSql, args...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

func (oraDB *OraDB) UpdateEntity(ctx context.Context, entity interface{}, whereSQL string, updateArgs ...interface{}) (int64, error) {
	tableInfo, err := oraDB.ProcessEntity2Table(entity)
	if err != nil {
		oraDB.logger.Errorc(ctx, "解析结构体指针失败[%s]", err)
		return 0, err
	}

	var updateColumns bytes.Buffer
	var args []interface{}
	idx := 1
	// 组装update语句
	for i := 0; i < tableInfo.ColNum; i++ {
		if tableInfo.ZeroFlag[i] { // 空的 跳过
			continue
		}
		updateColumns.WriteString(tableInfo.Columns[i] + "=" + fmt.Sprintf(":%d", idx) + ",")
		args = append(args, tableInfo.Data[i])
		idx++
	}
	updateColumns.Truncate(updateColumns.Len() - 1)

	execSQL := fmt.Sprintf("update %s set %s ", tableInfo.TableName, updateColumns.String())
	var preparedSQL []byte
	paramNum := 0
	if whereSQL != "" {
		for i := 0; i < len(whereSQL); i++ {
			p := whereSQL[i]
			if p == '?' {
				preparedSQL = append(preparedSQL, []byte(fmt.Sprintf(":%d", idx))...)
				idx++
				args = append(args, updateArgs[paramNum])
				paramNum++
			} else {
				preparedSQL = append(preparedSQL, p)
			}
		}
		if paramNum != len(updateArgs) {
			return 0, errors.Errorf("update 传入参数非法[%d][%d]", paramNum, updateArgs)
		}
		execSQL += " where " + string(preparedSQL)
	}

	oraDB.logger.Infoc(ctx, "执行更新SQL[%s][%+v]", execSQL, args)

	if tx := oraDB.GetCtxTx(ctx); tx != nil {
		result, err := tx.ExecContext(ctx, execSQL, args...)
		if err != nil {
			return 0, err
		}
		return result.RowsAffected()
	}

	result, err := oraDB.DB.ExecContext(ctx, execSQL, args...)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

func (oraDB *OraDB) PrePareSQL(ctx context.Context, execSql string) string {
	var preparedSql []byte
	idx := 1
	for i := 0; i < len(execSql); i++ {
		p := execSql[i]
		if p == '?' {
			preparedSql = append(preparedSql, []byte(fmt.Sprintf(":%d", idx))...)
			idx++
		} else {
			preparedSql = append(preparedSql, p)
		}
	}

	oraDB.logger.Debugc(ctx, "PreProcessSQL[%s][%s]", execSql, string(preparedSql))
	return string(preparedSql)
}

func (oraDB *OraDB) ProcessEntity2Table(entity interface{}) (*TableInfo, error) {
	var tableName string
	// 取请求结构体
	et := reflect.TypeOf(entity)
	ev := reflect.ValueOf(entity)

	v := ev.Elem()
	f := v.Type()
	pv := ev.Interface()

	if et.Implements(reflect.TypeOf((*TableName)(nil)).Elem()) {
		tableName = pv.(TableName).TableName()
	} else {
		return nil, errors.Errorf("TableName interface get error")
	}

	colNum := v.NumField()

	tbl := &TableInfo{
		TableName:    tableName,
		ColNum:       colNum,
		Columns:      make([]string, colNum),
		ColumnTypes:  make([]string, colNum),
		PlaceHolders: make([]string, colNum),
		Data:         make([]interface{}, colNum),
		ZeroFlag:     make([]bool, colNum),
		Scope:        make([]string, colNum),
	}

	// 遍历结构体
	for i := 0; i < v.NumField(); i++ {
		fv := v.Field(i)
		st := f.Field(i)

		if !fv.CanInterface() {
			return nil, errors.Errorf("[%s]Field is invalid!", st.Name)
		}
		value := fv.Interface()
		fieldType := reflect.TypeOf(value)

		tbl.Columns[i] = parseTag(st.Tag)
		tbl.ColumnTypes[i] = st.Type.String()
		tbl.PlaceHolders[i] = fmt.Sprintf(":%d", i+1)

		// Valuer
		if fieldType.Implements(reflect.TypeOf((*driver.Valuer)(nil)).Elem()) {
			tbl.Data[i], _ = value.(driver.Valuer).Value()
		} else { // 普通
			tbl.Data[i] = value
		}

		tbl.ZeroFlag[i] = fv.IsZero()

		tbl.Scope[i] = parseTagField(st.Tag, "scope")

		// oraDB.logger.Infoc(ctx, "[%s][%s][%s][%v]", tbl.Columns[i], tbl.ColumnTypes[i], tbl.PlaceHolders[i], tbl.Data[i])
	}
	return tbl, nil
}

func (oraDB *OraDB) ProcessEntity2Args(entity interface{}) ([]interface{}, error) {
	ev := reflect.ValueOf(entity)

	v := ev.Elem()
	f := v.Type()

	colNum := v.NumField()

	data := make([]interface{}, 0)
	// 遍历结构体
	for i := 0; i < colNum; i++ {
		fv := v.Field(i)
		st := f.Field(i)

		if !fv.CanInterface() {
			return nil, errors.Errorf("[%s]Field is invalid!", st.Name)
		}
		value := fv.Interface()

		if fv.IsZero() && st.Type.String() == NullTimeStampName {
			continue
		}

		// 结构体类型
		if v, ok := value.(driver.Valuer); ok {
			rValue, _ := v.Value()
			data = append(data, rValue)
		} else {
			data = append(data, value)
		}
	}
	return data, nil
}

func parseTag(tag reflect.StructTag) string {
	var name string
	dbTag := tag.Get("db")
	if dbTag != "" {
		_, _ = fmt.Sscanf(dbTag, "name:%s", &name)
		return name
	}
	return ""
}

func parseTagField(tag reflect.StructTag, tagName string) string {
	val := tag.Get(tagName)
	if val != "" {
		return val
	}
	return ""
}

type (
	OracleError   = network.OracleError
	NullTimeStamp = ora.NullTimeStamp
	Blob          = ora.Blob
)

const NullTimeStampName = "go_ora.NullTimeStamp"

/*
ErrNotFound 匹配不到数据.
*/
var ErrNotFound = &OracleError{
	ErrCode: 100,
	ErrMsg:  "data not found",
}

func OraError(err error) *OracleError {
	if err == nil {
		return nil
	}
	if oraErr, ok := err.(*OracleError); ok {
		return oraErr
	}
	return nil
}

/*
SetValue
设置SetValue.
*/
func SetValue(f sql.Scanner, val interface{}) {
	if valuer, ok := val.(driver.Valuer); ok {
		rv, err := valuer.Value()
		if err == nil {
			_ = f.Scan(rv)
		}
		return
	}
	_ = f.Scan(val)
}
